package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.RouteScheduleMaster;
import mwa.cs.util.JDate;

/**
 * A class used for managing route_schedule_master
 */
public class RouteScheduleMasterDAO {

	/** a database of this route_schedule_master */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public RouteScheduleMasterDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all route_schedule_master in database
     * @return all route_schedule_master
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM route_schedule_master";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }

    /**
     * prepare data in early month
     * @param pnSiteId site that will prepare
     * @param paPeriodMonth recent month
     * @param paPeriodYear recent year
     * @param pnUserId user that add data
     * @param poDate data that add data
     * @param poTime time that add data
     */
    public void InsertPrepareData(int pnSiteId, String paPeriodMonth, String paPeriodYear,
    		int pnUserId, String paDate, String paTime){
    	String vaSql = "INSERT INTO route_schedule_master (site_id, period_month, period_year, zone_id, route_id, total_record, "
    			+ "total_download, total_upload, download_date, download_time, upload_date, upload_time, reader_id, route_status, " 
    			+ "insert_user_id, inser_date, insert_time, update_user_id, update_date, update_time) "
    			+ "SELECT rout.site_id, "
    			+ "(CASE "
    			+ "WHEN (CAST(? AS INTEGER) + 1) > 12 THEN '01' "
    			+ "ELSE \"TRIM\"(\"TO_CHAR\"((CAST(? AS INTEGER) + 1), '00')) "
    			+ "END), " 
    			+ "(CASE "
    			+ "WHEN (CAST(? AS INTEGER) +1) > 12 THEN \"TRIM\"(\"TO_CHAR\"((CAST(? AS INTEGER) + 1), '0000')) "
    			+ "ELSE ? "
    			+ "END), " 
    			+ "rout.zone_id , rout.route_id, "
    			+ "(SELECT count(*) FROM cust_monthly WHERE site_id = rout.site_id and zone_id = rout.zone_id "
    			+ "and route_id = rout.route_id and period_month = ? and period_year = ? and status = 2), " 
    			+ "0, " 
    			+ "0, null, null, null, null, 0, '1', ?, ?, ?, 0, null, null "
    			+ "FROM route rout WHERE rout.site_id = ?";
        try {
        	db.insert(vaSql, paPeriodMonth, paPeriodMonth, paPeriodMonth, paPeriodYear, paPeriodYear, paPeriodMonth, paPeriodYear, 
        			pnUserId, paDate, paTime, pnSiteId);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    /**
     * finding all reader_id in zone_id and route_id
     * @param pnSiteId site that will find
     * @param pnZoneId zone that will find
     * @param paPeriodMonth month that will find
     * @param paPeriodYear year that will find
     * @return all reader_id with this condition
     */
    public List<Map<String, Object>> findReaderIdInRouteScheduleMaster(int pnSiteId, int pnZoneId, 
    		String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT zone_id, route_id, reader_id,total_download FROM route_schedule_master "
    				+ "WHERE site_id = ? AND zone_id = ? AND period_month = ? AND period_year = ? "
    				+ "ORDER BY route_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnZoneId, paPeriodMonth, paPeriodYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public String findDownloadDate( int pnSiteId, int pnZoneId, int pnRouteId, String paPeriodMonth, String paPeriodRecentYea ){
    	String vaSql = "SELECT download_date FROM route_schedule_master WHERE site_id = ? AND zone_id = ? AND route_id = ? AND period_month = ? AND period_year = ? ";
        Map<String, Object> result = db.querySingle( vaSql,pnSiteId,pnZoneId,pnRouteId, paPeriodMonth, paPeriodRecentYea);
        String vaResult = "";
        try {
        	vaResult = result.get("DOWNLOAD_DATE").toString();
		} catch (Exception e) {
			vaResult = null;
		}
        return vaResult;
    }
    
    public void updateDownloadDate(int pnSiteId,int pnZoneId,int pnReader, String paPeriodMonth, String paPeriodYear, int pnDowoadAmount){	
    	String vaSql = "UPDATE route_schedule_master SET download_date = ?,download_time = ? , total_download = ? "
    			+ " WHERE site_id = ? AND zone_id = ? AND reader_id = ? AND period_month = ? AND period_year = ? ";
        try {
        	db.update(vaSql, JDate.getDate(), JDate.getTime(), pnDowoadAmount, pnSiteId, pnZoneId, pnReader, paPeriodMonth, paPeriodYear);
		} catch (Exception e) {
			e.printStackTrace();
		}
    }
    
	public int getRouteIdByReaderId(int pnSiteId,int pnZoneId ,int pnReader, String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT route_id FROM route_schedule_master "
    			+ "WHERE site_id = ? AND zone_id = ? AND reader_id = ? AND period_month = ? AND period_year = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId, pnReader, paPeriodMonth, paPeriodYear);
    	int vaRouteId = 0;
    	try {
    		vaRouteId = Integer.parseInt( result.get("ROUTE_ID").toString());
		} catch (Exception e) {
			e.printStackTrace();
		} 	
    	return vaRouteId;
	}
    
    public List<Map<String, Object>> findZoneBySite(int pnSiteId, String paPeriodMonth, String paPeriodRecentYear) {
    	String vaSql = "SELECT zone_id, route_id, reader_id,total_download FROM route_schedule_master "
				+ "WHERE site_id = ? AND period_month = ? AND period_year = ? AND reader_id = 0"
				+ "ORDER BY zone_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paPeriodMonth, paPeriodRecentYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findPrepareDataGroupByReader(int pnSiteId , String paPeriodMonth, String paPeriodRecentYear) {
    	String vaSql = "SELECT rsm.zone_id,rsm.reader_id,rd.reader_name FROM route_schedule_master rsm JOIN reader rd ON rsm.reader_id = rd.reader_id "
    			+ "WHERE rsm.site_id = ? AND rsm.period_month = ? AND rsm.period_year = ? "
    			+ "GROUP BY rsm.reader_id ,rd.reader_name, rsm.zone_id ORDER BY rsm.zone_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paPeriodMonth, paPeriodRecentYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findRouteByZone(int pnSiteId,int pnZoneId, String paPeriodMonth, String paPeriodRecentYear) {
    	String vaSql = "SELECT route_id,total_download FROM route_schedule_master WHERE site_id = ? AND zone_id = ? AND period_month = ? "
    			+ "AND period_year = ? ORDER BY zone_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnZoneId, paPeriodMonth, paPeriodRecentYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public List<Map<String, Object>> findZoneBySiteDistinct(int pnSiteId, String paPeriodMonth, String paPeriodRecentYear) {
    	String vaSql = "SELECT DISTINCT(rsm.zone_id),zn.zone_name FROM route_schedule_master rsm JOIN ZONE zn ON rsm.zone_id = zn.zone_id"
    			+ " WHERE rsm.site_id = ? AND rsm.period_month = ? AND rsm.period_year = ? ORDER BY zone_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, paPeriodMonth, paPeriodRecentYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    public List<Map<String, Object>> findPrepareDataOfZone(int pnSiteId,int pnZoneId, String paPeriodMonth, String paPeriodRecentYear) {
    	String vaSql = "SELECT zone_id FROM route_schedule_master WHERE site_id = ? AND zone_id = ? AND period_month = ? "
    			+ "AND period_year = ? AND reader_id <> 0 ORDER BY zone_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnZoneId, paPeriodMonth, paPeriodRecentYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    
    
    public List<Map<String, Object>> findTotalDownloadByZone(int pnSiteId,int pnZoneId, String paPeriodMonth, String paPeriodRecentYear) {
    	String vaSql = "SELECT route_id FROM route_schedule_master WHERE site_id = ? AND zone_id = ? AND period_month = ? "
    			+ "AND period_year = ? AND Total_download <> 0 ORDER BY zone_id ASC";
    	List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId, pnZoneId, paPeriodMonth, paPeriodRecentYear);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    public void updateReaderInRSM(int pnSiteId, int pnZoneId,String paPeriodRecentMonth,String paPeriodRecentYear,int pnRouteID, int pnReaderID) {
        String vaSql = "UPDATE route_schedule_master SET reader_id = ? WHERE site_id = ? AND zone_id = ? AND "
        			+ "period_month = ? AND period_year = ? AND route_id = ?";
        try {
        	db.update(vaSql,pnReaderID, pnSiteId, pnZoneId, paPeriodRecentMonth, paPeriodRecentYear, pnRouteID);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    public void updateRouteScheduleMaster(RouteScheduleMaster poRouteScheduleMaster) {
        String vaSql = "UPDATE route_schedule_master SET total_upload = ? , upload_date = ? , upload_time = ? WHERE site_id = ? AND zone_id = ? "
        		+ "AND route_id = ? AND  period_month = ? AND period_year = ?";
        try {
        	db.update(vaSql,poRouteScheduleMaster.getVnTotalUpload(),
        			poRouteScheduleMaster.getVaUploadDate(),
        			poRouteScheduleMaster.getVaUploadTime(),
        			poRouteScheduleMaster.getVnSiteId(),
        			poRouteScheduleMaster.getVnZoneId(),
        			poRouteScheduleMaster.getVnRouteId(),
        			poRouteScheduleMaster.getVaPeriodMonth(),
        			poRouteScheduleMaster.getVaPeriodYear()
        			);
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
    
    public int getTotalUpload(RouteScheduleMaster poRouteScheduleMaster){
    	
    	String vaSql = "SELECT total_upload FROM route_schedule_master WHERE site_id = ? AND zone_id = ? "
        		+ "AND route_id = ? AND  period_month = ? AND period_year = ?";
        Map<String, Object> result = db.querySingle(vaSql, poRouteScheduleMaster.getVnSiteId(),
    			poRouteScheduleMaster.getVnZoneId(),
    			poRouteScheduleMaster.getVnRouteId(),
    			poRouteScheduleMaster.getVaPeriodMonth(),
    			poRouteScheduleMaster.getVaPeriodYear());
        if (result != null) {
            return Integer.parseInt(result.get("TOTAL_UPLOAD").toString());
        } else {
            return 0;
        }
    }
    
    public boolean deletePrepareData(String paCurrentMonth, String paCurentYear, int pnSiteId) {
        String vaSql = "DELETE FROM route_schedule_master WHERE period_month = ? AND period_year = ? AND site_id = ?";
        try {
            db.update(vaSql, paCurrentMonth, paCurentYear, pnSiteId);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    // Louis 11-11-2014
    public List<Map<String, Object>> findRouteBySizeZoneRouteMonthYear(int pnSiteId, int pnZoneId, int pnRouteIdFrom, int pnRouteIdTo,
    		String paPeriodMonth, String paPeriodYear, String paSortColumn)
    {
    	if(paSortColumn == null || paSortColumn.trim().length() == 0)
    	{
    		paSortColumn = " rsm.ROUTE_ID ASC ";
    	}
    	
//    	String vaSql = 
//    			" select distinct "
//    			+ " rsm.SITE_ID, rsm.ZONE_ID, rsm.PERIOD_MONTH, rsm.PERIOD_YEAR, rsm.ROUTE_ID, "
//    			+ " z.ZONE_NAME, r.ROUTE_DESCRIPTION, "
//    			+ " rsm.TOTAL_RECORD, rsm.INSER_DATE, "
//    			+ " rsm.TOTAL_DOWNLOAD, rsm.DOWNLOAD_DATE, "
//    			+ " rsm.TOTAL_UPLOAD, rsm.UPLOAD_DATE "
//    			+ " from ROUTE_SCHEDULE_MASTER rsm "
//    			+ " inner join ZONE z on (rsm.ZONE_ID = z.ZONE_ID and rsm.SITE_ID = z.SITE_ID) "
//    			+ " inner join ROUTE r on (rsm.ZONE_ID = r.ZONE_ID and rsm.SITE_ID = r.SITE_ID and rsm.ROUTE_ID = r.ROUTE_ID) "
//    			+ " inner join CUST_HH ch on (rsm.SITE_ID = ch.SITE_ID and rsm.ZONE_ID = ch.ZONE_ID and "
//    			+ " rsm.ROUTE_ID = ch.ROUTE_ID and rsm.PERIOD_MONTH = ch.PERIOD_MONTH and rsm.PERIOD_YEAR = ch.PERIOD_YEAR) "
//    			+ " where rsm.SITE_ID = ? and rsm.ZONE_ID = ? "
//    			+ " and rsm.PERIOD_MONTH = ? and rsm.PERIOD_YEAR = ? "
//    			+ " and rsm.ROUTE_ID >= ? and rsm.ROUTE_ID <= ? "
//    			+ " order by " + paSortColumn;
    	
    	String vaSql = "";
    	
    	vaSql += " select r.site_id, r.zone_id, r.route_id, z.zone_name, r.ROUTE_DESCRIPTION ";
    	vaSql += " ,nvl(( ";
    	vaSql += "  select TOTAL_RECORD from ROUTE_SCHEDULE_MASTER where "; 
    	vaSql += "  site_id = r.site_id and zone_id = r.zone_id and route_id =r.route_id ";
    	vaSql += "  and PERIOD_MONTH = ? and PERIOD_YEAR = ? ";
    	vaSql += "),0) as TOTAL_RECORD ";
    	vaSql += ",nvl(( ";
    	vaSql += "  select TOTAL_DOWNLOAD from ROUTE_SCHEDULE_MASTER where "; 
    	vaSql += "  site_id = r.site_id and zone_id = r.zone_id and route_id =r.route_id ";
    	vaSql += "  and PERIOD_MONTH = ? and PERIOD_YEAR = ? ";
    	vaSql += " ),0) as TOTAL_DOWNLOAD ";
    	vaSql += " ,nvl(( ";
    	vaSql += "  select TOTAL_UPLOAD from ROUTE_SCHEDULE_MASTER where "; 
    	vaSql += "  site_id = r.site_id and zone_id = r.zone_id and route_id =r.route_id ";
    	vaSql += "  and PERIOD_MONTH = ? and PERIOD_YEAR = ? ";
    	vaSql += " ),0) as TOTAL_UPLOAD ";
    	vaSql += " ,( ";
    	vaSql += "  select INSER_DATE from ROUTE_SCHEDULE_MASTER where ";
    	vaSql += "  site_id = r.site_id and zone_id = r.zone_id and route_id =r.route_id ";
    	vaSql += "  and PERIOD_MONTH = ? and PERIOD_YEAR = ? ";
    	vaSql += " ) as INSER_DATE ";
    	vaSql += " ,( ";
    	vaSql += "  select DOWNLOAD_DATE from ROUTE_SCHEDULE_MASTER where ";
    	vaSql += "  site_id = r.site_id and zone_id = r.zone_id and route_id =r.route_id ";
    	vaSql += "  and PERIOD_MONTH = ? and PERIOD_YEAR = ? ";
    	vaSql += " ) as DOWNLOAD_DATE ";
    	vaSql += " ,( ";
    	vaSql += "  select UPLOAD_DATE from ROUTE_SCHEDULE_MASTER where ";
    	vaSql += "  site_id = r.site_id and zone_id = r.zone_id and route_id =r.route_id ";
    	vaSql += "  and PERIOD_MONTH = ? and PERIOD_YEAR = ? ";
    	vaSql += " ) as UPLOAD_DATE ";

    	vaSql += " from zone z ";
    	vaSql += " inner join route r on (r.site_id = z.site_id and r.zone_id = z.zone_id) ";

    	vaSql += " where r.site_id = ? and r.zone_id = ? and r.route_id >= ? and r.route_id <= ? "; 
    	vaSql += " group by  r.site_id, r.zone_id, r.route_id, z.zone_name, r.ROUTE_DESCRIPTION ";
    	vaSql += " order by " + paSortColumn;
    	
    	List<Map<String, Object>> result = db.queryList(vaSql, paPeriodMonth, paPeriodYear, paPeriodMonth, paPeriodYear,
    			 paPeriodMonth, paPeriodYear, paPeriodMonth, paPeriodYear, paPeriodMonth, paPeriodYear,
    			 paPeriodMonth, paPeriodYear, pnSiteId, pnZoneId, pnRouteIdFrom, pnRouteIdTo);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
}
